#coding = utf-8
from datetime import time

from db import *
import utils

create_deal_sql = '''
create table if not exists deal 
    (id integer not null primary key auto_increment,
    exday_id integer not null,
    extime time not null,
    volume bigint not null,
    price decimal(9,3) not null, buyorsell char(1) not null)
'''

create_view_deals_sql = '''
create view everydaydeal as 
    select exday.code, exday.exdate, deal.extime,deal.volume,deal.price,deal.buyorsell
    from deal , exday
    where deal.exday_id = exday.id
'''

def delta2time(n):
    h = n/3600

    n = n%3600
    m = n/60

    s = n%60
    #print h,n,m
    t = time(hour=h, minute=m, second=s)

    return t.isoformat()

def data2db(con, code, exdate, data):
    #data : [extime, volume, price, buyorsell]
    insert_sql = "insert into deal (exday_id, extime, volume, price, buyorsell)\
                  values(%s,%s,%s,%s,%s)"
                
    exday_id = exday.get_exday_id(con, code, exdate)
    for d in data:
        d.insert(0, exday_id)
    
    c = con.cursor()
    c.executemany(insert_sql, data)

def get_deal_from_db(con, code, exdate):

    def dt2s(one):
        one = list(one)
        
        one[1] = one[1].isoformat()
        one[2] = delta2time(one[2].seconds)
        
        return one
        
    sql = 'select * from everydaydeal where code=%s and exdate=%s'
    
    c = con.cursor()
    c.execute(sql, (code, exdate))
    r = c.fetchall()
    c.close()
    
    if not r:
        return None

    r = [dt2s(i) for i in r]
    
    return r
